using System;
using System.Data;
using Mono.Data.SqliteClient;
using System.Collections.Generic;
using System.Text;
using System.IO;






namespace Stastics
{
	class MainClass
	{

		public static void Main (string[] args)
		{
			
			
			Console.WriteLine ("Aguarde enquanto o porgrama é carregado...");
			int intyear = 0;
			
			string estab1 = "";
			string estab2 = "";
			string year = "";
			
			//de forma a reduzir o tempo de execução das pesquisas na base de dados, 
			//cada metodo que devova uma lsita de uma pesquisa que
			//seja util no futuro, sera guardado numa lista.
			
			List<string> allEstablishmentsInDataBase = new List<string> ();
			allEstablishmentsInDataBase = getAllEstablishments ();
			allEstablishmentsInDataBase.Sort ();
			string string_option = "";
			int option = 0;
			
			
			do {
				Console.WriteLine ("Motor de pesquisas sobre a base de dados REBIDES \n Que pertende obeter:\n\n" +
					"1- Consultar número de doutorados por estabelecimento e por ano\n" +
					"2- Conjunto de doutorados por estabelecimento e por ano\n" +
					"3- Conjunto de doutorados que mudaram de um estabelcimento para outro por ano\n" +
					"4- Conjunto de professores que mudaram de categoria por ano\n" +
					"5- Conjunto de professores que mudaram de categoria por ano e por estabelecimento\n" +
					"6- Conjunto de professores que abandonou um estabelecimento por ano\n" +
					"7- Conjunto de professores que entrarm num estabelecimento\n" +
				    "8- Fim do porgrama\n\n" +
					"*****************************##***************************");
				Console.WriteLine ("Escolha uma opção:");
				string_option = Console.ReadLine ();
				
				option = int.Parse (string_option);
				
				switch (option) {
				case 1:
					
					
					Console.Clear ();
					showAll (allEstablishmentsInDataBase);
					
					Console.WriteLine ("Escolha um estabelcimento da lista");
					estab1 = Console.ReadLine ();
					
					Console.Clear ();
					
					Console.WriteLine ("Introduza o ano (entre 2000 e 2009)");
					year = Console.ReadLine ();
					intyear = int.Parse(year);
					Console.Clear ();
					
					numberDoctorsPerE_AndPerY_ (allEstablishmentsInDataBase[int.Parse (estab1)], (intyear - 2000) + "");
					
					
					
					
					break;
				case 2:
					
					
					Console.Clear ();
					showAll (allEstablishmentsInDataBase);
					
					Console.WriteLine ("Escolha um estabelcimento da lista");
					estab1 = Console.ReadLine ();
					Console.Clear ();
					Console.WriteLine ("Introduza o ano (entre 2000 e 2009)");
					year = Console.ReadLine ();
					intyear = int.Parse(year);
					Console.Clear ();
					
					setDoctorsPerE_AndPerY_ (allEstablishmentsInDataBase[int.Parse (estab1)], (intyear - 2000) + "");
					
					
					
					
					break;
				case 3:
					
					Console.Clear ();
					Console.WriteLine ("Introduza o ano (entre 2001 e 2009)");
					year = Console.ReadLine ();
					intyear = int.Parse(year);
					Console.Clear ();
					List<string> allEstablishmentsWhitDoctrsInDataBase = new List<string> ();
					allEstablishmentsWhitDoctrsInDataBase = establishmentOfteacherWhitDoctor ((intyear - 2000) + "");
					allEstablishmentsWhitDoctrsInDataBase.Sort ();
					
					
					
					
					
					
					showAll (allEstablishmentsWhitDoctrsInDataBase);
					
					Console.WriteLine ("Escolha o estabelecimento onde estava no ano anterior");
					estab1 = Console.ReadLine ();
					Console.Clear ();
					showAll (allEstablishmentsWhitDoctrsInDataBase);
					
					Console.WriteLine ("Escolha o estabelecimento onde esta no  ano actual");
					estab2 = Console.ReadLine ();
					Console.Clear ();
					
					
					
					setDoctorsPerEstablishmentAndPerYearThatChange (allEstablishmentsWhitDoctrsInDataBase[int.Parse (estab1)], allEstablishmentsWhitDoctrsInDataBase[int.Parse (estab2)], (intyear - 2000));
					
					
					
					
					
					break;
				case 4:
					Console.Clear ();
					Console.WriteLine ("Aguarde enquanto a operação que oredenou seja concluida \n(pode levar alguns minutos)...");
					Console.WriteLine ("AVISO -> PODE HAVER INCONSISTENCIA NOS DADOS \nPOIS EXISTEM PROFESSORES QUE LENCIONAM EM MAIS\nDO QUE UM ESTABELCIMENTO NO MESMO ANO");
					Console.Write ("\nPressione uma tecla para contunuar ...");
					Console.ReadKey ();
					
					
					Console.WriteLine ("Introduza o ano (entre 2001 e 2009)");
					year = Console.ReadLine ();
					intyear = int.Parse(year);
					
					teachers_ThatChange_CategoryPerYear (intyear - 2000);
					
					
					break;
				case 5:
					Console.Clear ();
					showAll (allEstablishmentsInDataBase);
					
					Console.WriteLine ("Escolha o estabelecimento");
					estab1 = Console.ReadLine ();
					Console.Clear ();
					Console.WriteLine ("Introduza o ano (entre 2001 e 2009)");
					year = Console.ReadLine ();
					intyear = int.Parse(year);
					Console.Clear ();
					setOfTeachers_That_Change_Category_perYearAndPerEstab (allEstablishmentsInDataBase[int.Parse (estab1)], (intyear - 2000));
					
					break;
				case 6:
					
					Console.Clear ();
					showAll (allEstablishmentsInDataBase);
					
					Console.WriteLine ("Escolha o estabelecimento");
					estab1 = Console.ReadLine ();
					Console.Clear ();
					Console.WriteLine ("Introduza o ano (entre 2001 e 2009)");
					year = Console.ReadLine ();
					intyear = int.Parse(year);
					Console.Clear ();
					teacherLeaving_establishmentPerYear (allEstablishmentsInDataBase[int.Parse (estab1)], (intyear - 2000));
					
					break;
				case 7:
					
					Console.Clear ();
					showAll (allEstablishmentsInDataBase);
					
					Console.WriteLine ("Escolha o estabelecimento");
					estab1 = Console.ReadLine ();
					Console.Clear ();
					
					Console.WriteLine ("Introduza o ano (entre 2001 e 2009)");
					year = Console.ReadLine ();
					intyear = int.Parse(year);
					Console.Clear ();
					
					teacherNew_establishmentPerYear (allEstablishmentsInDataBase[int.Parse (estab1)], (intyear - 2000));
					
					break;
				case 8:
					
					Console.WriteLine ("O programa vai terminar");
					break;
				default:
					
					Console.WriteLine ("Número Não Correcto");
					break;
					
					
				}
				
			} while (option != 8);
			
			
			
			
			
			
			
			
			Console.WriteLine ("Hello World!");
		}

		/**
		 * premite mostrar todos os estabelecimentos para depois o utilizador escolher o indice
		 * paramtro - lista a mostrar
		 * */
		static void showAll (List<string> LToShow)
		{
			List<string> ListToShow = new List<string> ();
			ListToShow = LToShow;
			int count = 0;
			
			foreach (string show in ListToShow) {
				
				Console.WriteLine (count + " < - > " + show);
				count++;
			}
			
		}

		/**
		 * todos os estabelecimentos da base de dados
		 * devolve - lista de todos os estabelcimentos
		 * */
		static List<string> getAllEstablishments ()
		{
			
			List<string> EstablishmentList = new List<string> ();
			
			string connectionString = "URI=file:rebides1.db,version=3";
			IDbConnection dbcon;
			
			dbcon = (IDbConnection)new SqliteConnection (connectionString);
			dbcon.Open ();
			IDbCommand dbcmd = dbcon.CreateCommand ();
			
			string sql = "SELECT DISTINCT estabelecimento FROM fichas_docencia";
			
			dbcmd.CommandText = sql;
			IDataReader reader = dbcmd.ExecuteReader ();
			
			while (reader.Read ()) {
				
				EstablishmentList.Add (reader.GetString (0));
				
			}
			
			
			
			
			//Limpa e fecha as variáveis
			reader.Close ();
			reader = null;
			dbcmd.Dispose ();
			dbcmd = null;
			dbcon.Close ();
			dbcon = null;
			
			return EstablishmentList;
			
			
		}
		/******************************************************************************************************************/
		/*					Query - Numero de professores doutores de um estabelecimento								  */
		/******************************************************************************************************************
		/**
		 * Numero de professores doutores por estabelcimento e por ano
		 * paramtro - recebe estabelecimento
		 * paramtro - recebe ano
		 * */
		static void numberDoctorsPerE_AndPerY_ (string Establishment, string year)
		{
			string estab = Establishment;
			string year_ = year;
			
			
			Console.WriteLine ("Numero de Doutorados por estabelecimento e por ano");
			
			
			
			numberDoctorsPerEstablishmentAndPerYear (estab, year_);
			
			
			
		}


		/**
		 * Numero de porfessores com douturmaneto por ano e por estabelecimento
		 * paramtro - recebe estabelecimento
		 * paramtro - recebe ano
		 * */
		static void numberDoctorsPerEstablishmentAndPerYear (string Establishment, string Year)
		{
			string estab = Establishment;
			string finalResult = "";
			FileInfo file = new FileInfo ("numberOfTeachersWhitDocterDegree.html");
			StreamWriter swNew = file.CreateText ();
			
			string connectionString = "URI=file:rebides1.db,version=3";
			IDbConnection dbcon;
			
			dbcon = (IDbConnection)new SqliteConnection (connectionString);
			dbcon.Open ();
			IDbCommand dbcmd = dbcon.CreateCommand ();
			
			string sql = "SELECT COUNT(DISTINCT docente) FROM fichas_docencia WHERE grau = 'Doutoramento' AND estabelecimento = '" + Establishment + "' AND ano = '" + Year + "'";
			
			dbcmd.CommandText = sql;
			IDataReader reader = dbcmd.ExecuteReader ();
			
			
			
			while (reader.Read ()) {
				
				finalResult = reader.GetString (0);
				
				
			}
			
			//Limpa e fecha as variáveis
			reader.Close ();
			reader = null;
			dbcmd.Dispose ();
			dbcmd = null;
			dbcon.Close ();
			dbcon = null;
			
			swNew.WriteLine ("<head>");
			swNew.WriteLine ("<body>");
			Console.WriteLine ("Número de docentes douturados no estabelecimento <" + estab + "no ano 200" + Year);
			swNew.WriteLine ("<center>Número de docentes douturados no estabelecimento < " + estab + " no ano 200" + Year + "</center><br><br>");
			swNew.WriteLine ("> " + finalResult + "<br>");
			Console.WriteLine ("> " + finalResult);
			
			swNew.WriteLine ("<body/>");
			swNew.WriteLine ("<head/>");
			
			swNew.Close ();
			
		}
		/******************************************************************************************************************/
		/*					Query - Conjunto de professores doutores de um estabelecimento								  */
		/******************************************************************************************************************/
		/**
		 * Conjunto de professores doutores por estabelcimento e por ano
		 * paramtro - recebe estabelecimento
		 * paramtro - recebe ano
		 * */
		static void setDoctorsPerE_AndPerY_ (string Establishment, string year)
		{
			
			string estab = Establishment;
			string year_ = year;
			
			
			Console.WriteLine ("Conjunto de Doutorados por estabelecimento e por ano");
			
			
			
			
			setDoctorsPerEstablishmentAndPerYear (estab, year_);
			
			
			
		}
		/**
		 * Conjunto de professores doutores por ano e por estabelecimento
		 * paramtro - recebe estabelecimento
		 * paramtro - recebe ano
		 *
		 * */
		static void setDoctorsPerEstablishmentAndPerYear (string Establishment, string Year)
		{
			string estab = Establishment;
			List<string> finalResult = new List<string> ();
			FileInfo file = new FileInfo ("setOfTeachersWhitDocterDegree.html");
			StreamWriter swNew = file.CreateText ();
			
			string connectionString = "URI=file:rebides1.db,version=3";
			IDbConnection dbcon;
			
			dbcon = (IDbConnection)new SqliteConnection (connectionString);
			dbcon.Open ();
			IDbCommand dbcmd = dbcon.CreateCommand ();
			
			string sql = "SELECT DISTINCT docente FROM fichas_docencia WHERE grau = 'Doutoramento' AND estabelecimento = '" + Establishment + "' AND ano = '" + Year + "'";
			
			dbcmd.CommandText = sql;
			IDataReader reader = dbcmd.ExecuteReader ();
			
			Console.WriteLine ();
			Console.WriteLine ("Ano = " + Year + " Estabelecimento -> " + Establishment);
			
			while (reader.Read ()) {
				finalResult.Add (reader.GetString (0));
				
				
			}
			
			//Limpa e fecha as variáveis
			reader.Close ();
			reader = null;
			dbcmd.Dispose ();
			dbcmd = null;
			dbcon.Close ();
			dbcon = null;
			
			
			swNew.WriteLine ("<head>");
			swNew.WriteLine ("<body>");
			Console.WriteLine ("Docentes douturados no estabelecimento <" + estab + "no ano 200" + Year);
			swNew.WriteLine ("<center>Docentes douturados no estabelecimento < " + estab + " no ano 200" + Year + "</center><br><br>");
			
			finalResult.Sort ();
			
			foreach (string teacher in finalResult) {
				Console.WriteLine ("> " + teacher);
				swNew.WriteLine ("> " + teacher + "<br>");
			}
			
			swNew.WriteLine ("<body/>");
			swNew.WriteLine ("<head/>");
			
			swNew.Close ();
		}

		/**
		 *Devolve uma lista de todos os docentes da base de dados
		 *com doutoramento
		 *devolve lista de todos os docentes com doutoramento
		 * paramtro - recebe ano
		 * 
		 * */
		static List<string> getTotalDocentsDoctrsList (string year)
		{
			List<string> teacherList = new List<string> ();
			
			string connectionString = "URI=file:rebides1.db,version=3";
			IDbConnection dbcon;
			
			dbcon = (IDbConnection)new SqliteConnection (connectionString);
			dbcon.Open ();
			IDbCommand dbcmd = dbcon.CreateCommand ();
			
			string sql = "SELECT docente FROM fichas_docencia WHERE grau = 'Doutoramento' AND ano = '" + year + "'";
			
			dbcmd.CommandText = sql;
			IDataReader reader = dbcmd.ExecuteReader ();
			
			
			
			while (reader.Read ()) {
				
				teacherList.Add (reader.GetString (0));
				
			}
			
			//Limpa e fecha as variáveis
			reader.Close ();
			reader = null;
			dbcmd.Dispose ();
			dbcmd = null;
			dbcon.Close ();
			dbcon = null;
			
			return teacherList;
			
		}

		/**
		 * Procura qual os estabelecimentos que tem professores douturados por ano
		 * 
		 * devolve estabelecimentos que tenham professores com doutroamento
		 * paramtro - recebe ano
		 * */
		static List<string> establishmentOfteacherWhitDoctor (string year)
		{
			
			//string establishment = "";
			List<string> establishment = new List<string> ();
			string connectionString = "URI=file:rebides1.db,version=3";
			IDbConnection dbcon;
			
			dbcon = (IDbConnection)new SqliteConnection (connectionString);
			dbcon.Open ();
			IDbCommand dbcmd = dbcon.CreateCommand ();
			
			
			
			
			string sql = "SELECT DISTINCT estabelecimento FROM fichas_docencia WHERE grau = 'Doutoramento' AND ano = '" + year + "'";
			
			dbcmd.CommandText = sql;
			IDataReader reader = dbcmd.ExecuteReader ();
			
			
			
			while (reader.Read ()) {
				
				establishment.Add (reader.GetString (0));
				//Console.WriteLine("->" + establishment);
				
			}
			
			
			
			//Limpa e fecha as variáveis
			reader.Close ();
			reader = null;
			dbcmd.Dispose ();
			dbcmd = null;
			dbcon.Close ();
			dbcon = null;
			
			return establishment;
			
		}
		/******************************************************************************************************************/
		/*					Query - Conjunto de professores que mudaram de um estabelecimento para outro por ano		  */
		/******************************************************************************************************************/
		/**
		 * Mostra o conjunto de professores que mudaram de estabelecimento por ano
		 * paramtro - recebe estabelecimento anteiror
		 * paramtro - recebe estabelecimento actual
		 * paramtro - recebe ano
		 * 
		 * */
		static void setDoctorsPerEstablishmentAndPerYearThatChange (string estab_last, string estab_this, int year)
		{
			List<string> totalTeachersThisYear = new List<string> ();
			List<string> totalTeachersLastYear = new List<string> ();
			List<string> resultFinal = new List<string> ();
			int result = 0;
			int check = 0;
			FileInfo file = new FileInfo ("ChangeEstablishmentPerYearForOtherEstablishment.html");
			StreamWriter swNew = file.CreateText ();
			
			string lastEstab = estab_last;
			string thisEstab = estab_this;
			int count = 0;
			
			
			
			totalTeachersThisYear = searchForDocentsInEstablishmentPerYear (thisEstab, year + "");
			
			
			
			totalTeachersLastYear = searchForDocentsInEstablishmentPerYear (lastEstab, (year - 1) + "");
			
			
			foreach (string teacher in totalTeachersLastYear) {
				
				result = totalTeachersThisYear.FindIndex (delegate(string valor) { return valor.Contains (teacher); });
				
				
				if (result >= 0) {
					check = resultFinal.FindIndex (delegate(string valor) { return valor.Contains (totalTeachersThisYear[result]); });
					if (check < 0) {
						resultFinal.Add (totalTeachersThisYear[result]);
					}
				}
				
				
				
				//totalTeachersThisYear.Remove(totalTeachersThisYear[result]);
				//listOfTeachersEstablishmentThisYear.Remove(listOfTeachersEstablishmentThisYear[result]);   
				
				count++;
				
			}
			
			swNew.WriteLine ("<head>");
			swNew.WriteLine ("<body>");
			Console.WriteLine ("Docentes que mudaram do estabelecimento <" + lastEstab + "> para <" + thisEstab + "> no ano 200" + year);
			swNew.WriteLine ("<center>Docentes que mudaram do estabelecimento < " + lastEstab + " > <br>para < " + thisEstab + " > no ano 200" + year + "></center><br><br>");
			resultFinal.Sort ();
			
			foreach (string teacher_f in resultFinal) {
				
				Console.WriteLine ("> " + teacher_f);
				swNew.WriteLine ("> " + teacher_f + "<br>");
				
			}
			
			swNew.WriteLine ("<body/>");
			swNew.WriteLine ("<head/>");
			
			swNew.Close ();
			
			
			
			
			
			
			
			
		}


		/**
		 * Procurar docentes com doutoramento num dado estabelcimento num dado ano
		 * Usado na setDoctorsPerEstablishmentAndPerYearThatChange
		 * paramatro - recebe estabelecimento
		 * paramatro - recebe ano
		 * devolve lista de docentes num estabelecimento com doutoramento
		 * */
		static List<string> searchForDocentsInEstablishmentPerYear (string Establishment, string year)
		{
			
			List<string> teacherList = new List<string> ();
			
			string connectionString = "URI=file:rebides1.db,version=3";
			IDbConnection dbcon;
			
			dbcon = (IDbConnection)new SqliteConnection (connectionString);
			dbcon.Open ();
			IDbCommand dbcmd = dbcon.CreateCommand ();
			
			string sql = "SELECT docente FROM fichas_docencia WHERE grau = 'Doutoramento' AND ano = '" + year + "' AND estabelecimento = '" + Establishment + "'";
			
			dbcmd.CommandText = sql;
			IDataReader reader = dbcmd.ExecuteReader ();
			
			
			
			while (reader.Read ()) {
				
				teacherList.Add (reader.GetString (0));
				
			}
			
			//Limpa e fecha as variáveis
			reader.Close ();
			reader = null;
			dbcmd.Dispose ();
			dbcmd = null;
			dbcon.Close ();
			dbcon = null;
			
			return teacherList;
		}
		/******************************************************************************************************************/
		/*									Query - Conjunto de professores que mudaram de categoria por ano			  */
		/******************************************************************************************************************/

		/**
		 *Devolve uma lista de todos os docentes da base de dados num determinado ano
		 *parametro -recebe ano
		 *devolve lista total de docentes por ano
		 * 
		 * */
		static List<string> getTotalDocentsListPerYear (string year)
		{
			List<string> teacherList = new List<string> ();
			
			string connectionString = "URI=file:rebides1.db,version=3";
			IDbConnection dbcon;
			
			dbcon = (IDbConnection)new SqliteConnection (connectionString);
			dbcon.Open ();
			IDbCommand dbcmd = dbcon.CreateCommand ();
			
			string sql = "SELECT docente FROM fichas_docencia WHERE ano = '" + year + "'";
			
			dbcmd.CommandText = sql;
			IDataReader reader = dbcmd.ExecuteReader ();
			
			
			
			while (reader.Read ()) {
				
				teacherList.Add (reader.GetString (0));
				
			}
			
			//Limpa e fecha as variáveis
			reader.Close ();
			reader = null;
			dbcmd.Dispose ();
			dbcmd = null;
			dbcon.Close ();
			dbcon = null;
			
			return teacherList;
			
		}

		/**
		 *Devolve uma lista de todos as categorias por ano
		 *parametro -recebe ano
		 *devolve lista total de estabelcimentos por ano
		 * 
		 * */
		static List<string> getTotalCategoriesListPerYear (string year)
		{
			List<string> CategoriesList = new List<string> ();
			
			string connectionString = "URI=file:rebides1.db,version=3";
			IDbConnection dbcon;
			
			dbcon = (IDbConnection)new SqliteConnection (connectionString);
			dbcon.Open ();
			IDbCommand dbcmd = dbcon.CreateCommand ();
			
			string sql = "SELECT categoria FROM fichas_docencia WHERE ano = '" + year + "'";
			
			dbcmd.CommandText = sql;
			IDataReader reader = dbcmd.ExecuteReader ();
			
			
			
			while (reader.Read ()) {
				
				CategoriesList.Add (reader.GetString (0));
				
			}
			
			//Limpa e fecha as variáveis
			reader.Close ();
			reader = null;
			dbcmd.Dispose ();
			dbcmd = null;
			dbcon.Close ();
			dbcon = null;
			
			return CategoriesList;
			
		}
		/**
		 * Professores que mudaram de grau por ano
		 * parametro -recebe ano
		 *
		 * */
		static void teachers_ThatChange_CategoryPerYear (int year)
		{
			FileInfo file = new FileInfo ("GlobalSetOfTeachersChangeCategory.html");
			StreamWriter swNew = file.CreateText ();
			
			int count = 0;
			int result = 0;
			
			
			int precent = 0;
			
			int oneTiem = 1;
			List<string> categoryListThisYear = new List<string> ();
			List<string> categoryListLastYear = new List<string> ();
			
			List<string> totalDocentsLastYear = new List<string> ();
			List<string> totalDocentsThisYear = new List<string> ();
			
			categoryListThisYear = getTotalCategoriesListPerYear (year + "");
			categoryListLastYear = getTotalCategoriesListPerYear ((year - 1) + "");
			
			totalDocentsLastYear = getTotalDocentsListPerYear ((year - 1) + "");
			totalDocentsThisYear = getTotalDocentsListPerYear (year + "");
			
			Console.WriteLine ("Quantidade de dados a analisar > " + categoryListThisYear.Count);
			
			int totalTeachers = totalDocentsLastYear.Count;
			
			List<string> finalResult = new List<string> ();
			
			foreach (string teacher in totalDocentsLastYear) {
				
				precent = (count * 100) / totalTeachers;
				
				if (precent == 10 && oneTiem == 1) {
					
					Console.Write ("<" + precent + "%>");
					oneTiem = 2;
					
				} else if (precent == 20 && oneTiem == 2) {
					Console.Write ("<" + precent + "%>");
					oneTiem = 3;
				} else if (precent == 30 && oneTiem == 3) {
					
					Console.Write ("<" + precent + "%>");
					oneTiem = 4;
					
				} else if (precent == 40 && oneTiem == 4) {
					Console.Write ("<" + precent + "%>");
					oneTiem = 5;
				} else if (precent == 50 && oneTiem == 5) {
					
					Console.Write ("<" + precent + "%>");
					oneTiem = 6;
					
				} else if (precent == 60 && oneTiem == 6) {
					Console.Write ("<" + precent + "%>");
					oneTiem = 7;
				} else if (precent == 70 && oneTiem == 7) {
					
					Console.Write ("<" + precent + "%>");
					oneTiem = 8;
					
				} else if (precent == 80 && oneTiem == 8) {
					Console.Write ("<" + precent + "%>");
					oneTiem = 9;
				} else if (precent == 90 && oneTiem == 9) {
					Console.Write ("<" + precent + "%>");
					oneTiem = 10;
				} else if (precent == 10 && oneTiem == 10) {
					Console.Write ("<" + precent + "%>");
				}
				
				result = totalDocentsThisYear.FindIndex (delegate(string valor) { return valor.Contains (teacher); });
				
				//Console.WriteLine(result);
				
				if (result < 0) {
					
				} else if (categoryListLastYear[count] != categoryListThisYear[result]) {
					//countChanges++;
					//Console.WriteLine(countChanges+ " -> " + teacher + " <Last> " + categoryListLastYear[count] + " <This> " + categoryListThisYear[result]);
					result = finalResult.FindIndex (delegate(string valor) { return valor.Contains (teacher); });
					if (result < 0) {
						finalResult.Add (teacher);
					}
					
					
				}
				
				//totalTeachersThisYear.Remove(totalTeachersThisYear[result]);
				//listOfTeachersEstablishmentThisYear.Remove(listOfTeachersEstablishmentThisYear[result]);   
				
				count++;
				
			}
			Console.WriteLine ();
			swNew.WriteLine ("<head>");
			swNew.WriteLine ("<body>");
			Console.WriteLine ("Conjunto global de professores que mudaram de categoria > no ano > 200" + year);
			swNew.WriteLine ("<center>Conjunto global de professores que mudaram de categoria > no ano > 200" + year + "</center><br><br>");
			finalResult.Sort ();
			foreach (string teacher in finalResult) {
				Console.WriteLine ("> " + teacher);
				swNew.WriteLine ("> " + teacher + "<br>");
				
			}
			
			swNew.WriteLine ("<body/>");
			swNew.WriteLine ("<head/>");
			
			swNew.Close ();
		}

		/******************************************************************************************************************/
		/*				Query - Conjunto de professores que mudaram de categoria por ano e por estabalecimento			  */
		/******************************************************************************************************************/
		/**
		 * Conjunto de professores que mudaram de categoria por ano e por estabelecimento
		 * parametro - recebe estabelecimento
		 * parametro - recebe ano
		 * */
		static void setOfTeachers_That_Change_Category_perYearAndPerEstab (string establishment, int year)
		{
			FileInfo file = new FileInfo ("ChangeCategoryPerYearAndPerEstablishment.html");
			StreamWriter swNew = file.CreateText ();
			
			string estab = establishment;
			int result = 0;
			int count = 0;
			
			List<string> categoriesPerEstablishmentAndPerLastYear = new List<string> ();
			categoriesPerEstablishmentAndPerLastYear = categoriesInEstablismentPerYear (estab, (year - 1) + "");
			
			List<string> categoriesPerEstablishmentAndPerThisYear = new List<string> ();
			categoriesPerEstablishmentAndPerThisYear = categoriesInEstablismentPerYear (estab, year + "");
			
			List<string> teachersPerEstablishmentAndPerLastYear = new List<string> ();
			teachersPerEstablishmentAndPerLastYear = teachersInEstablismentPerYear (estab, (year - 1) + "");
			
			List<string> teachersPerEstablishmentAndPerThisYear = new List<string> ();
			teachersPerEstablishmentAndPerThisYear = teachersInEstablismentPerYear (estab, year + "");
			
			List<string> setOfTeachers = new List<string> ();
			
			swNew.WriteLine ("<head>");
			swNew.WriteLine ("<body>");
			Console.WriteLine ("Docentes que mudaram de categoria no estabelecimento <" + estab + " no ano 200" + year + ">");
			swNew.WriteLine ("<center>Docentes que mudaram de categoria <br> no estabelecimento < " + estab + " no ano 200" + year + "></center><br><br>");
			
			
			foreach (string teacher in teachersPerEstablishmentAndPerLastYear) {
				
				result = teachersPerEstablishmentAndPerThisYear.FindIndex (delegate(string valor) { return valor.Contains (teacher); });
				if (result >= 0) {
					
					if (categoriesPerEstablishmentAndPerLastYear[count] != categoriesPerEstablishmentAndPerThisYear[result]) {
						result = setOfTeachers.FindIndex (delegate(string valor) { return valor.Contains (teacher); });
						if (result < 0) {
							setOfTeachers.Add (teacher);
						}
						
					}
					
				}
				
				count++;
				
			}
			
			setOfTeachers.Sort ();
			
			foreach (string teacher in setOfTeachers) {
				
				Console.WriteLine ("> " + teacher);
				swNew.WriteLine ("> " + teacher + "<br>");
				
			}
			swNew.WriteLine ("<body/>");
			swNew.WriteLine ("<head/>");
			
			swNew.Close ();
			
			
		}
		/**
		 * Categorias num estabelecimento num dado ano
		 * devolve lista de categorias
		 * parametro - recebe estabelecimento
		 * parametro - recebe ano
		 * */
		static List<string> categoriesInEstablismentPerYear (string establishment, string year)
		{
			List<string> categoriesList = new List<string> ();
			
			string connectionString = "URI=file:rebides1.db,version=3";
			IDbConnection dbcon;
			
			dbcon = (IDbConnection)new SqliteConnection (connectionString);
			dbcon.Open ();
			IDbCommand dbcmd = dbcon.CreateCommand ();
			
			string sql = "SELECT categoria FROM fichas_docencia WHERE ano = '" + year + "' AND estabelecimento = '" + establishment + "'";
			
			dbcmd.CommandText = sql;
			IDataReader reader = dbcmd.ExecuteReader ();
			
			
			
			while (reader.Read ()) {
				
				categoriesList.Add (reader.GetString (0));
				
			}
			
			//Limpa e fecha as variáveis
			reader.Close ();
			reader = null;
			dbcmd.Dispose ();
			dbcmd = null;
			dbcon.Close ();
			dbcon = null;
			
			return categoriesList;
			
		}
		/**
		 * Conjunto de porfessores num dado estabelcimento num dado ano
		 * devolve lista de professores
		 * parametro - recebe estabelecimento
		 * parametro - recebe ano
		 * 
		 * */
		static List<string> teachersInEstablismentPerYear (string establishment, string year)
		{
			List<string> teacherList = new List<string> ();
			
			string connectionString = "URI=file:rebides1.db,version=3";
			IDbConnection dbcon;
			
			dbcon = (IDbConnection)new SqliteConnection (connectionString);
			dbcon.Open ();
			IDbCommand dbcmd = dbcon.CreateCommand ();
			
			string sql = "SELECT docente FROM fichas_docencia WHERE ano = '" + year + "' AND estabelecimento = '" + establishment + "'";
			
			dbcmd.CommandText = sql;
			IDataReader reader = dbcmd.ExecuteReader ();
			
			
			
			while (reader.Read ()) {
				
				teacherList.Add (reader.GetString (0));
				
			}
			
			//Limpa e fecha as variáveis
			reader.Close ();
			reader = null;
			dbcmd.Dispose ();
			dbcmd = null;
			dbcon.Close ();
			dbcon = null;
			
			return teacherList;
			
		}
		/******************************************************************************************************************/
		/*				Query - Conjunto de professores que abandonaram um estabelecimento  por ano 					  */
		/******************************************************************************************************************/
		/**
		 * Profesores que deixaram um estabelecimento
		 * parametro - recebe estabelecimento
		 * parametro - recebe ano
		 * */
		static void teacherLeaving_establishmentPerYear (string establishment, int year)
		{
			FileInfo file = new FileInfo ("leavingPersonal.html");
			StreamWriter swNew = file.CreateText ();
			
			
			int result = 0;
			string estab = establishment;
			List<string> teachersPerEstablishmentAndPerLastYear = new List<string> ();
			teachersPerEstablishmentAndPerLastYear = teachersInEstablismentPerYear (estab, (year - 1) + "");
			
			List<string> teachersPerEstablishmentAndPerThisYear = new List<string> ();
			teachersPerEstablishmentAndPerThisYear = teachersInEstablismentPerYear (estab, year + "");
			
			List<string> teachersLeaving = new List<string> ();
			
			
			foreach (string teacher in teachersPerEstablishmentAndPerLastYear) {
				
				result = teachersPerEstablishmentAndPerThisYear.FindIndex (delegate(string valor) { return valor.Contains (teacher); });
				
				if (result < 0) {
					result = teachersLeaving.FindIndex (delegate(string valor) { return valor.Contains (teacher); });
					if (result < 0) {
						teachersLeaving.Add (teacher);
					}
					
				}
			}
			swNew.WriteLine ("<head>");
			swNew.WriteLine ("<body>");
			Console.WriteLine ("Conjunto de professores que sairam do estabelcimento < " + estab + " > no ano > 200" + year);
			swNew.WriteLine ("<center>Conjunto de professores que sairam do estabelcimento < " + estab + " > no ano > 200" + year + "</center><br><br>");
			teachersLeaving.Sort ();
			foreach (string teacher in teachersLeaving) {
				Console.WriteLine ("> " + teacher);
				swNew.WriteLine ("> " + teacher + "<br>");
			}
			
			swNew.WriteLine ("<body/>");
			swNew.WriteLine ("<head/>");
			
			swNew.Close ();
			
		}

		/******************************************************************************************************************/
		/*				Query - Conjunto de professores que entraram num estabelecimento  por ano 						  */
		/******************************************************************************************************************/
		
	/**
		 * Profesores que entraram um estabelecimento
		 * parametro - recebe estabelecimento
		 * parametro - recebe ano
		 * */	static void teacherNew_establishmentPerYear (string establishment, int year)
		{
			FileInfo file = new FileInfo ("newPersonal.html");
			StreamWriter swNew = file.CreateText ();
			
			
			
			
			int result = 0;
			string estab = establishment;
			List<string> teachersPerEstablishmentAndPerLastYear = new List<string> ();
			teachersPerEstablishmentAndPerLastYear = teachersInEstablismentPerYear (estab, (year - 1) + "");
			
			List<string> teachersPerEstablishmentAndPerThisYear = new List<string> ();
			teachersPerEstablishmentAndPerThisYear = teachersInEstablismentPerYear (estab, year + "");
			
			List<string> teachersNew = new List<string> ();
			
			
			foreach (string teacher in teachersPerEstablishmentAndPerThisYear) {
				
				result = teachersPerEstablishmentAndPerLastYear.FindIndex (delegate(string valor) { return valor.Contains (teacher); });
				
				if (result < 0) {
					result = teachersNew.FindIndex (delegate(string valor) { return valor.Contains (teacher); });
					if (result < 0) {
						teachersNew.Add (teacher);
					}
					
				}
			}
			
			swNew.WriteLine ("<head>");
			swNew.WriteLine ("<body>");
			Console.WriteLine ("Conjunto de professores que entraram no estabelcimento < " + estab + " > no ano > 200" + year);
			swNew.WriteLine ("<center>Conjunto de professores que entraram no estabelcimento < " + estab + " > no ano > 200" + year + "</center><br><br>");
			teachersNew.Sort ();
			foreach (string teacher in teachersNew) {
				Console.WriteLine ("> " + teacher);
				swNew.WriteLine ("> " + teacher + "<br>");
			}
			
			swNew.WriteLine ("<body/>");
			swNew.WriteLine ("<head/>");
			
			swNew.Close ();
			
		}
	}
	
	
	
}
